<?php

namespace app\common\library;

use GuzzleHttp\Psr7\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Exception;
use think\facade\App;

/**
 * 导出excel class
 * @author daichongweb
 */
class Exports
{
    // 表格坐标
    private $cellIndex = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];

    // 默认配置
    public $config = [
        'bold' => true, // 加粗
        'size' => 12, // 文字大小
        'column' => 4, // 设置列数
        'title' => '默认导出', // 表格标题
        'name' => '特抱抱', // 文件名
        'type' => 'Xls', // 导出格式
    ];
    public $spreadsheet = null;
    // 默认表头
    public $tableHeader = [
        'php',
        'vue',
        'java',
        'go',
    ];

    // 默认数据
    public $tableDefaultData = [
        [
            '天下第一',
            'Vue牛逼',
            'java牛逼',
            'go牛逼',
        ],
    ];

    /**
     * @param array $tableHeader 表头
     * @param array $tableDefaultData 需要导出数据
     */
    public function __construct($tableHeader, $tableDefaultData)
    {
        if (empty($tableHeader)) {
            throw new Exception('export error', '请设置表头');
        }

        if (empty($tableDefaultData)) {
            throw new Exception('export error', '请设置导出数据');
        }

        $this->tableHeader = $tableHeader;
        $this->config['column'] = count($tableHeader);
        $this->tableDefaultData = $tableDefaultData;
    }

    /**
     * 创建表格 createTable
     */
    public function createTable($savefilename = '导出文件')
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $this->spreadsheet = $spreadsheet;
        //居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        // 设置基本属性
        $worksheet->setTitle($this->config['title']);
        $worksheet->getStyle($this->getColumn())->applyFromArray($styleArray)
            ->getFont()
            ->setBold($this->config['bold'])
            ->setName('Verdana')
            ->setSize($this->config['size']);


        foreach ($this->tableHeader as $index => $name) {
            $worksheet->setCellValue($this->cellIndex[$index] . '1', $name);
            if($name=='档案中存在的主要问题'){
                $worksheet->getColumnDimension('E')->setWidth(600);
            }
        }

        $baseRow = 2;
        foreach ($this->tableDefaultData as $index => $data) {
            $i = $index + $baseRow;
            for ($k = 0; $k <= $this->config['column'] - 1; $k++) {
                $item = $data[$k];
                $worksheet->getStyle($this->cellIndex[$k] . $i)->applyFromArray($styleArray)->getAlignment()->setWrapText(true);
                $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item);
                // 中文设置表格宽度
                if (preg_match("/[\x7f-\xff]/", $item)) {
//                    if (strlen($item) > 100) {
//                        $worksheet->getColumnDimension($this->cellIndex[$k])->setAutoSize(true);//->setWidth(/3);
//                    } else {
                        $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(610)->setAutoSize(true);
                   // }
                } else {
                    // 非中文自动设置宽度
                    $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(150)->setAutoSize(true);
                }
            }
        }
        $worksheet->calculateColumnWidths();
        return $this->saveToBrowserByTmp($savefilename);
        //self::downloadExcel($spreadsheet, $this->config['name'], 'Xlsx');
    }

    /**
     * 创建表格 createTable
     */
    public function createTable2($savefilename = '导出文件')
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $this->spreadsheet = $spreadsheet;
        //居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $styleArray2 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        // 设置基本属性
        $worksheet->setTitle($this->config['title']);
        $worksheet->getStyle($this->getColumn())
            //->applyFromArray($styleArray)
            ->getFont()
            ->setBold($this->config['bold'])
            ->setName('Verdana')
            ->setSize($this->config['size']);
        foreach ($this->tableHeader as $index => $name) {
            $worksheet->setCellValue($this->cellIndex[$index] . '1', $name);
            if($name=='档案中存在的主要问题'){
                $worksheet->getColumnDimension('E')->setWidth(600);
            }
            if(in_array($index, [4,5,6,8,9])){
                $worksheet->getStyle($this->cellIndex[$index] . '1')
                    ->applyFromArray($styleArray2)->getAlignment();
            }
        }

        $baseRow = 2;
        foreach ($this->tableDefaultData as $index => $data) {
            $i = $index + $baseRow;
            for ($k = 0; $k <= $this->config['column'] - 1; $k++) {
                $item = $data[$k];
                if(in_array($k, [4,5,6,8,9])){
                    $worksheet->getStyle($this->cellIndex[$k] . $i)
                        ->applyFromArray($styleArray2)->getAlignment()
                        ->setWrapText(true);
                    $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item);
                    // 中文设置表格宽度
                    $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(60);//->setAutoSize(true);
                }
                else if(in_array($k, [3])){
                    $worksheet->getStyle($this->cellIndex[$k] . $i)
                        ->applyFromArray($styleArray)->getAlignment()
                        ->setWrapText(true);
                    $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item);
                    // 中文设置表格宽度
                    $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(6)->setAutoSize(true);
                }else{
                    $worksheet->getStyle($this->cellIndex[$k] . $i)
                        ->applyFromArray($styleArray)->getAlignment()
                        ->setWrapText(true);
                    $worksheet->setCellValue($this->cellIndex[$k] . $i, ' ' . $item);
                    // 中文设置表格宽度
                    if (preg_match("/[\x7f-\xff]/", $item)) {
//                    if (strlen($item) > 100) {
//                        $worksheet->getColumnDimension($this->cellIndex[$k])->setAutoSize(true);//->setWidth(/3);
//                    } else {
                        $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(10)->setAutoSize(true);
                        // }
                    } else {
                        // 非中文自动设置宽度
                        $worksheet->getColumnDimension($this->cellIndex[$k])->setWidth(10)->setAutoSize(true);
                    }
                }

            }
        }
        $worksheet->calculateColumnWidths();
        return $this->saveToBrowserByTmp($savefilename);
        //self::downloadExcel($spreadsheet, $this->config['name'], 'Xlsx');
    }

    /**
     * 文件下载 downloadExcel
     *
     * @param data $spreadsheet
     * @param string $filename
     * @param string $format
     * @return void
     */
    private function downloadExcel($spreadsheet, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }


        header("Content-Disposition: attachment;filename="
            . urlencode($filename) . '.' . strtolower($format)); //. date('Y-m-d')
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, $format);
        $objWriter->save('php://output');
        exit;
    }

    //保存临时文件在从浏览器自动下载到本地
    public function saveToBrowserByTmp($fileName)
    {

        ob_end_clean();
        ob_start();
//设置文件扩展名， $format只能为 Xlsx 或 Xls
        $format = 'Xlsx';
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        $name = $fileName;
        header("Content-Disposition: attachment;filename=" . $name . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($this->spreadsheet, $format);
//        $objWriter->save('php://output');
        //通过php保存在服务器的时候需要用到
        $dir = App::getRootPath() . 'public' . DIRECTORY_SEPARATOR . '/downloads';
        $objWriter->save($dir . '/' . $name . '.xlsx');
        return '/downloads' . DIRECTORY_SEPARATOR . $name . '.xlsx';
    }


    private function getColumn($row = 1)
    {
        $index = $this->cellIndex[$this->config['column']];
        return 'A' . $row . ':' . $index . $row;
    }

    private function autoFitColumnWidthToContent($sheet, $fromCol, $toCol)
    {
        if (empty($toCol)) { //not defined the last column, set it the max one
            $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
        }
        for ($i = $fromCol; $i <= $toCol; $i++) {
            $sheet->getColumnDimension($i)->setAutoSize(true);
        }
        $sheet->calculateColumnWidths();
    }
}
